<?php
require_once FRAME_ROOT_PATH . '/Exception/MysqlException.php';
/**
 * 数据库类文件
 * 采用mysql原生函数实现
 *
 * @author RD
 */
class Mysql {
    /**
     * 当前连接
     */
    private $link   = NULL;
    
    /**
     * 配置信息
     */
    private $config = array ();
    
    /**
     * 总查询数量
     */
    private $query = 0;
    
    
    /**
     * 是否开启监控
     */
    private $Profiler = false;
    
    /**
     * 监控数据 array ('sql' =>'sql', 'time' => time ms)
     */
    private $Profiler_data = array ();
    /**
     * 监控数据，总执行时间
     */
    private $Profiler_time = 0;
    
    /**
     * 保存数据库实例
     *
     * @var Array
     */
    private static $instances = array();
    
    /**
     * 初始化
     */
    public function __construct ($dbhost, $dbuser, $dbpw, $dbname, $dbcharset = '') {
        $this->config ['dbhost']        = $dbhost;
        $this->config ['dbuser']        = $dbuser;
        $this->config ['dbpw']          = $dbpw;
        $this->config ['dbname']        = $dbname;
        $this->config ['dbcharset']     = $dbcharset;
    }
    
    /**
     * 获取数据库实例
     *
     * @param String $name 默认default
     * @return Mysql
     */
    public static function getInstance($name='default')
    {
    	$instance = Mysql::$instances[$name];
    	
    	if ($instance == null)
    		throw new MysqlException("Mysql Instance $name Not Set");
    	
    	return $instance;
    }
    
    /**
     * 设置mysql实例
     *
     * @param String $name	getInstance 名
     * @param String $dbhost
     * @param String $dbuser
     * @param String $dbpw
     * @param String $dbname
     * @param String $dbcharset 数据库默认编码
     */
    public static function setInstance($name, $dbhost, $dbuser, $dbpw, $dbname, $dbcharset = '')
    {
    	$db = new Mysql($dbhost, $dbuser, $dbpw, $dbname, $dbcharset);
    	Mysql::$instances[$name] = $db;
    }
    
    /**
     * 关闭所有Mysql连接
     *
     */
    public static function closeInstance()
    {
    	foreach (Mysql::$instances as $key => $db)
    		$db->close();
    }
    
    /**
     * 连接数据库
     */
    private function _connect () {
        $link = @mysql_connect($this->config ['dbhost'], $this->config ['dbuser'], $this->config ['dbpw'], true);
        if (!$link) {
            throw new MysqlException ('Can not connect to MySQL server: ' . mysql_error());
        } else {
            $this->link = $link;
            
            if ($this->config ['dbcharset'] != '') {
                $dbcharset = str_replace('-', '', strtolower ($this->config ['dbcharset']));
                $version = $this->version ();
                if ($version > '4.1') {
                    @mysql_query("SET character_set_connection=$dbcharset, character_set_results=$dbcharset, character_set_client=binary", $this->link);
                }
                if($version > '5.0.1') {
                    //长度超过，自动截断， 自动插入空值
                    @mysql_query("SET sql_mode=''", $this->link);
                }
            }
            
            if (!mysql_select_db($this->config ['dbname'], $this->link))
                throw new MysqlException ('Select Db Error:' . mysql_error ());
        }
        
        return $this->link;
    }
    
    
    /**
     * 获取数据库连接资源
     */
    private function getLink () {
        return ($this->link == NULL)?$this->_connect () : $this->link;
    }
    
    
    /**
     * 插入单条数据
     *
     * @param $tableName 表名
     * @param array $values 键值对
     * @param $returnInsertId 是否返回自增id
     * 
     * @return void | last_insert_id
     * @throws MysqlException
     */
    public function insert ( $tableName, array $values, $returnInsertId=false) {
        $keys = '';
        $val = '';
        $comma = '';
        reset ($values);
        while (list ($k, $v) = each ($values)) {
            $keys .= $comma . '`' . $k . '`';
            $val  .= $comma . '"' . $this->quote ($v) . '"';
            $comma = ',';
        }
        
        $this->query ('INSERT INTO `' . $tableName . '` (' . $keys . ') VALUES (' . $val . ')');
        
        if ($returnInsertId)
            return $this->lastInsertId ();
        
        return true;
    }
    
    
    /**
     * 修改数据
     *
     * @param $tableName 表名
     * @param array $newValues 新的值，键值对  array ('name' => 'name', 'num' => 'num+1')
     * @param $where 条件
     * @param $affectRows 是否返回修改的行数
     *
     * @return void | 修改的行数
     * @throws MysqlException
     */
    public function update ( $tableName, array $newValues, $where, $affectRows = false) {
        $sql = 'UPDATE `' . $tableName . '` SET';
        $comma = '';
        reset ($newValues);
        while (list ($k, $v) = each ($newValues)) {
        	if (strpos($v, '+') !== false && (list($sk, $sv) = explode("+", $v, 2)) && is_numeric($sv)) {// key+3
        		list($sk, $sv) = explode("+", $v, 2);
            	$sql .= $comma . ' `' . $sk . '`=' . $this->quote ($v);
        	} else             
            	$sql .= $comma . ' `' . $k . '`="' . $this->quote ($v) . '"';
            $comma = ', ';
        }
        
        if (!empty ($where)) {
            if ( stripos( trim($where), 'where') !== 0)
                $where = ' WHERE ' . $where;
        }
        
        $this->query ($sql . $where);
        
        if ($affectRows)
            return $this->affectRows ();
        
        return true;
    }
    
    
    /**
     * 取得一个字段的一行数据
     * 参数绑定列子：1,单个参数：query('select * from table where id=?', $id)
     *                     或者：query('select * from table where id=?', array ($id))
     *               2，多个参数：query('select * from table where id=? and name=?', array ($id, $name))
     *
     * @param $sql sql语句
     * @param $bind 参数绑定
     *
     * @return mixed var 若无数据，则返回false
     * @throws MysqlException
     */
    public function fetchOne ($sql, $bind = array ()) {
        $query = $this->query ($sql, $bind);
        if ($this->numRows ($query) >0)
            return  mysql_result ($query, 0, 0);
        else
            return false;
    }
    
    
    /**
     * 返回一行数据
     * 参数绑定列子：1,单个参数：query('select * from table where id=?', $id)
     *                     或者：query('select * from table where id=?', array ($id))
     *               2，多个参数：query('select * from table where id=? and name=?', array ($id, $name))
     *
     * @param $sql sql语句
     * @param $bind 参数绑定
     *
     * @return Array var 若无数据，则返回false
     * @throws MysqlException
     */
    public function fetchRow ($sql, $bind = array ()) {
        $query = $this->query ($sql, $bind);
        if ($this->numRows ($query) >0)
            return  mysql_fetch_assoc ($query);
        else
            return false;
    }
    
    
    /**
     * 返回所有数据
     * 参数绑定列子：1,单个参数：query('select * from table where id=?', $id)
     *                     或者：query('select * from table where id=?', array ($id))
     *               2，多个参数：query('select * from table where id=? and name=?', array ($id, $name))
     *
     * @param $sql sql语句
     * @param $bind 参数绑定
     *
     * @return Array var 若无数据，则返回array () 空数组
     * @throws MysqlException
     */
    public function fetchAll ( $sql , $bind = array ()) {
        $result = array ();
        $query = $this->query ($sql, $bind);
        while ($row = mysql_fetch_array ($query, MYSQL_ASSOC)) {
            $result [] = $row;
        }
        
        return $result;
    }
    
        
    /**
     * 返回所有数据,以某一列作为键名
     * 参数绑定列子：1,单个参数：query('select * from table where id=?', $id)
     *                     或者：query('select * from table where id=?', array ($id))
     *               2，多个参数：query('select * from table where id=? and name=?', array ($id, $name))
     *
     * @param $sql sql语句
     * @param $bind 参数绑定
     * @param $col  默认为第一列,传列名
     *
     * @return Array var 若无数据，则返回array () 空数组
     * @throws MysqlException
     */
    public function fetchAssoc ( $sql , $bind = array (), $col=0) {
        $result = array ();
        $query = $this->query ($sql, $bind);
        while ($row = mysql_fetch_array ($query, MYSQL_ASSOC)) {
            if (empty($col)) {
                $col = key ($row);
            }
            $result [$row[$col]] = $row;
        }
        
        return $result;
    }
    
    
    /**
     * 秦刚添加
     * 将数据库中的一条记录用array形式返回，并且游标向下移一行。
     *
     * @param query $query sql查询句柄
     * @param string $result_type 返回数组中索引的类型
     * @return array
     */
    public function fetchArray ($query, $result_type = MYSQL_ASSOC)
    {
        return mysql_fetch_array($query, $result_type);
    } 
    
    
    /**
     * 删除
     *
     * @param $tableName 表名
     * @param $where 条件
     * @param $affectRows 是否返回修改的行数
     *
     * @return void | 删除的行数
     * @throws MysqlException
     */
    public function delete ($tableName, $where, $affectRows=false) {
        if (!empty ($where)) {
            if ( stripos( trim($where), 'where') !== 0)
                $where = ' WHERE ' . $where;
        }
        
        $this->query ('DELETE FROM `' . $tableName . '` ' . $where);
        
        if ($affectRows)
            return $this->affectRows ();
        
        return true;
    }
    
    
    /**
     * 开启事务，出错，抛出MysqlException
     *
     * @return NULL
     * @throws MysqlException
     */
    public function beginTransaction ()
    {
        $this->query("START TRANSACTION");
    }


    /**
     * 提交事务，出错，抛出MysqlException
     *
     * @return NULL
     * @throws MysqlException
     */
    public function commit ()
    {
        $this->query("COMMIT");
    }


    /**
     * 回滚事务，出错，抛出MysqlException
     *
     * @return NULL
     * @throws MysqlException
     */
    public function rollBack ()
    {
        $this->query("ROLLBACK");
    }
    
    
    /**
     * 查询sql语句，返回原始值
     *
     * 参数绑定列子：1,单个参数：query('select * from table where id=?', $id)
                           或者：query('select * from table where id=?', array ($id))
     *               2，多个参数：query('select * from table where id=? and name=?', array ($id, $name))
     *
     * @param String $sql sql语句
     * @param $bind 参数绑定
     *
     */
    public function query ($sql, $bind = array ()) {
        $this->query++;
        if ($this->Profiler) {
            $t = microtime (true);
        }
        
        if (!empty ($bind)) {
            if (!is_array ($bind)) {
                $bind = array ($bind);
            }
            
            $sql = $this->quoteInto ($sql, $bind);
        }
        
        $r = mysql_query ($sql, $this->getLink ());
        
        if ($r === false) {
            throw new MysqlException ('SQL QUERY ERROR:' . $sql . '  MYSQL:' . mysql_error ());
        }
        
        if ($this->Profiler) {
            $t = microtime (true)-$t;
            $this->Profiler_time+=$t;
            $this->Profiler_data [$this->query] = array ('sql' => $sql, 'time' => $t);
        }
        
        return $r;
    }
    
    
    /**
     * 返回最后插入的自增主键值
     */
    public function lastInsertId() {
        return ($id = mysql_insert_id($this->link)) >= 0 ? $id : $this->fetchOne("SELECT last_insert_id()");
    }
    
    
    /**
     * 返回影响的行数
     */
    public function affectRows () {
        return mysql_affected_rows($this->link);
    }
    
    
    /**
     * 返回结果中的行数
     *
     * @param $query 查询
     * 
     * @return 行数
     */
    public function numRows($query) {
        $query = mysql_num_rows($query);
        return $query;
    }


    /**
     * 对字符串进行转义
     */
    public function quote ($str) {
        return mysql_real_escape_string ($str, $this->getLink());
    }
    
    
    /**
     * 转义带参数的字符串
     * select * from test where id=?, array ($id)
     *
     * @param $str 转义字符串，占位符用?号
     * @param array $param 值
     *
     * @return String
     */
    public function quoteInto ($str, array $param) {
        $numc       = count ($param);
        $num        = 0;
        $newparam   = array ();
        reset ($param);
        while (list (,$v) = each ($param)) {
            if (is_numeric ($v)) {
                $newparam [] = $v;
            } else {
                $newparam [] = mysql_real_escape_string ($v, $this->getLink());
            }
        }
        $str2 = str_replace ('?', '%s', $str, $num);
        if ($num != $numc) {
            throw new MysqlException ( 'Params Error (' . $numc . ' give must be:' . $num . '):' . $str);
        }
        
        return vsprintf ($str2, $newparam);
    }
    
    
    /**
     * 获取数据库版本 如：5.0.67-community-nt
     */
    public function version() {
        return mysql_get_server_info($this->link);
    }
    
    
    /**
     * 开启监视
     *
     * @param $isopen 是否开启
     */
    public function setProfiler ( $isopen=false) {
        $this->Profiler = $isopen;
    }

    
    /**
     * 获取监视信息
     */
    public function getProfilerData () {
        return array ('querynum'    => $this->query,
                      'totaltime'   => $this->Profiler_time,
                      'sql'         => $this->Profiler_data
                     );
    }
    
    
    /**
     * 获取查询次数
     */
    public function getQueryNum () {
        return $this->query;
    }
    
    /**
     * 关闭
     *
     */
    public function close() {
    	mysql_close($this->link);
    }
}